package com.dshore.messagecenter.dao.overview;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class OverViewDao {
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	/**
	 * 表1,时间获取当日实时流量
	 * @version 8/24
	 */
	public List<Map<String, Object>>getMessageByNowTime(String riqi){
		String firstData=riqi+"00:00:00";
		StringBuffer sql=new StringBuffer();
		sql.append("select * from (select TO_CHAR(CREATED_TIME,'hh24:mi') as time,SUM(KPI_VALUE) as sum ")
			.append("from T_MR_KPI_RECORD_CURRENT where OBJ_TYPE = (SELECT di.DICT_CODE ")
			.append("FROM T_SYS_DICT di WHERE di.DICT_TYPE = 'obj_type' AND di.DICT_NAME ")
			.append("= '主题队列') and KPI_CODE = (SELECT di.DICT_CODE FROM T_SYS_DICT di ")
			.append("WHERE di.DICT_TYPE = 'kpi_type' AND di.DICT_NAME = '消息流量') and ")
			.append("TO_CHAR(CREATED_TIME, 'yyyy-mm-dd hh24:mi:ss') >= ? GROUP BY ")
			.append("CREATED_TIME ORDER BY CREATED_TIME desc) where ROWNUM <=7");
		return jdbcTemplate.queryForList(sql.toString(),firstData);
	}
	
	/**
	 * 表2,获取当日消息发送延迟
	 * @version 8/24
	 */
	public List<Map<String, Object>>getDelayByNowTime(String riqi){
		String firstData=riqi+"00:00:00";
		StringBuffer sql=new StringBuffer();
		sql.append("select * from (select sum(t.ALLDELAY) / sum(t.NUM) as sum, TO_CHAR(t.CREATED_TIME,'hh24:mi') as time from (select ")
			.append("t2.KPI_VALUE as num, t1.KPI_VALUE * t2.KPI_VALUE allDelay,t1.CREATED_TIME from ")
			.append("T_MR_KPI_RECORD_CURRENT t1 left join T_MR_KPI_RECORD_CURRENT t2 on t1.OBJ = t2.OBJ and ")
			.append("t1.CREATED_TIME = t2.CREATED_TIME where t1.KPI_CODE = (SELECT di.DICT_CODE FROM T_SYS_DICT di ")
			.append("WHERE di.DICT_TYPE = 'kpi_type' AND di.DICT_NAME = '消息发送延迟') and t2.KPI_CODE = (SELECT ")
			.append("di.DICT_CODE FROM T_SYS_DICT di WHERE di.DICT_TYPE = 'kpi_type' AND di.DICT_NAME = '消息流量') ")
			.append("and TO_CHAR(t1.CREATED_TIME, 'yyyy-mm-dd hh24:mi:ss') > ?) t GROUP BY ")
			.append("CREATED_TIME ORDER BY t.CREATED_TIME DESC) where ROWNUM <=7");
		return jdbcTemplate.queryForList(sql.toString(),firstData);
	}
	
	/**
	 * 获取应用数
	 */
	public List<Object> getAppAmount(String riqi) {
		String firstData=riqi+"00:00:00";
		String lastData=riqi+"23:59:59";
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) as a");
		sql.append(" from T_MD_APP t");
		sql.append(" where t.DEL_FLAG=0");
		sql.append(" AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=");		
		sql.append("?");
		sql.append(" AND  TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=");
		sql.append("?");	
		String sql2="select count(*) as b from T_MD_APP t where t.DEL_FLAG=0";
		List<Map<String, Object>> a= jdbcTemplate.queryForList(sql.toString(),firstData,lastData);
		Object today=a.get(0).get("a");
		List<Map<String, Object>> b= jdbcTemplate.queryForList(sql2);
		Object all=b.get(0).get("b");
		List<Object>list=new ArrayList<>();
		list.add(today);
		list.add(all);
		return list;
	}
	
	/**
	 * 获取主题队列数
	 */
	public List<Object> getToppicAmount(String riqi) {
		String firstData=riqi+"00:00:00";
		String lastData=riqi+"23:59:59";
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) as a");
		sql.append(" from T_MD_TOPIC t");
		sql.append(" where t.DEL_FLAG=0");
		sql.append(" AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=");		
		sql.append("?");
		sql.append(" AND  TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=");
		sql.append("?");	
		String sql2="select count(*) as b from T_MD_TOPIC t where t.DEL_FLAG=0";
		List<Map<String, Object>> a= jdbcTemplate.queryForList(sql.toString(),firstData,lastData);
		Object today=a.get(0).get("a");
		List<Map<String, Object>> b= jdbcTemplate.queryForList(sql2);
		Object all=b.get(0).get("b");
		List<Object>list=new ArrayList<>();
		list.add(today);
		list.add(all);
		return list;
	}
	
	/**
	 * 获取消息流量
	 */
	public List<Object> getMessageAmount(String riqi) {
		String firstData=riqi+"00:00:00";
		String lastData=riqi+"23:59:59";
		StringBuffer sql=new StringBuffer();
		sql.append("select nvl(SUM(T_MR_KPI_RECORD_CURRENT.KPI_VALUE),0) as a");
		sql.append(" from T_MR_KPI_RECORD_CURRENT");
		sql.append(" WHERE T_MR_KPI_RECORD_CURRENT.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')");
		sql.append(" and obj_type=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')");
		/*sql.append(" AND t.DEL_FLAG=0");*/
		sql.append(" AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=");		
		sql.append("?");
		sql.append(" AND  TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=");
		sql.append("?");	
		
		//总流量
		String sql2="select nvl(SUM(T_MR_KPI_RECORD_CURRENT.KPI_VALUE),0) as b \r\n" + 
				"				from T_MR_KPI_RECORD_CURRENT \r\n" + 
				"				where T_MR_KPI_RECORD_CURRENT.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量') \r\n" + 
				"				and T_MR_KPI_RECORD_CURRENT.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')";
		List<Map<String, Object>> a= jdbcTemplate.queryForList(sql.toString(),firstData,lastData);
		Object today=a.get(0).get("a");
		List<Map<String, Object>> b= jdbcTemplate.queryForList(sql2);
		Object all=b.get(0).get("b");
		List<Object>list=new ArrayList<>();
		list.add(today);
		list.add(all);
		return list;
	}
	
	/**
	 * 获取消息发送平均延迟
	 */
	public List<Object>getAvgDelay(){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT nvl(sum( t.NUM ),0) num, nvl(sum( t.ALLDELAY ) / sum( t.NUM ),0) AS deley FROM	(SELECT	")
			.append("t2.KPI_VALUE AS num,t1.KPI_VALUE * t2.KPI_VALUE allDelay FROM ")
			.append("T_MR_KPI_RECORD_CURRENT t1	LEFT JOIN T_MR_KPI_RECORD_CURRENT t2 ON t1.OBJ = t2.OBJ ")
			.append("AND t1.CREATED_TIME = t2.CREATED_TIME WHERE	t1.KPI_CODE = ( SELECT di.DICT_CODE ")
			.append("FROM T_SYS_DICT di WHERE di.DICT_TYPE = 'kpi_type' AND di.DICT_NAME = '消息发送延迟' ) ")
			.append("AND t2.KPI_CODE = ( SELECT di.DICT_CODE FROM T_SYS_DICT di WHERE di.DICT_TYPE = ")
			.append("'kpi_type' AND di.DICT_NAME = '消息流量' )) t ");
		
		List<Map<String, Object>> a= jdbcTemplate.queryForList(sql.toString());
		Double allDelay=Double.parseDouble(a.get(0).get("deley").toString());
		Object allMessage=a.get(0).get("num");
		List<Object>list=new ArrayList<>();
		list.add(allDelay.intValue());
		list.add(allMessage);
		return list;
	}
	
	/**
	 *表3, 获取当前消息队列内存占用情况
	 * @vision  8/24
	 */
	public List<Map<String, Object>> getDataByDate() {
		StringBuffer sql=new StringBuffer();
		sql.append("select * from(\r\n" + 
				"select TO_CHAR(t.CREATED_TIME,'hh24:mi') as time,sum(t.KPI_VALUE) as sum\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select di.DICT_CODE from T_SYS_DICT di where di.DICT_TYPE='kpi_type' and di.DICT_NAME='内存占用')\r\n" + 
				"and t.OBJ_TYPE=(select di.DICT_CODE from T_SYS_DICT di where di.DICT_TYPE='obj_type' and di.DICT_NAME='消息队列')\r\n" + 
				"group by t.KPI_CODE,t.OBJ_TYPE,t.CREATED_TIME\r\n" + 
				"ORDER BY t.CREATED_TIME desc ) aa\r\n" + 
				"where ROWNUM<=7\r\n" + 
				"ORDER BY aa.time ");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据年月日时分秒获取当前磁盘情况
	 * @vision  8/24
	 */
	public  List<Map<String, Object>> getcipan() {
		StringBuffer sql=new StringBuffer();
		sql.append("select * from(\r\n" + 
				"				select TO_CHAR(t.CREATED_TIME,'hh24:mi') as time,sum(t.KPI_VALUE) as sum\r\n" + 
				"				from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"				where t.KPI_CODE=(select di.DICT_CODE from T_SYS_DICT di where di.DICT_TYPE='kpi_type' and di.DICT_NAME='磁盘剩余空间')\r\n" + 
				"				and t.OBJ_TYPE=(select di.DICT_CODE from T_SYS_DICT di where di.DICT_TYPE='obj_type' and di.DICT_NAME='消息队列')\r\n" + 
				"				group by t.KPI_CODE,t.OBJ_TYPE,t.CREATED_TIME\r\n" + 
				"				ORDER BY t.CREATED_TIME desc ) aa\r\n" + 
				"				where ROWNUM<=7\r\n" + 
				"				ORDER BY aa.time ");
		return jdbcTemplate.queryForList(sql.toString());
	}
}
